﻿namespace AnyOffice.DAL
{
    using AnyOffice.Model;
    using Microsoft.ApplicationBlocks.Data;
    using System;
    using System.Data;
    using System.Data.SqlClient;

    public class BookManagerDao
    {
        public bool Delete(SqlConnection conn, int autoId)
        {
            string commandText = "DELETE FROM [dbo].[Book_Manager] WHERE [AutoId] = @AutoId";
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@AutoId", SqlDbType.Int) };
            commandParameters[0].Value = autoId;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, commandText, commandParameters);
            return true;
        }

        public int Insert(SqlConnection conn, BookManagerData data)
        {
            string commandText = "INSERT INTO [dbo].[Book_Manager] ( [ManagerId], [ManageDeptId] ) VALUES ( @ManagerId, @ManageDeptId ) SELECT SCOPE_IDENTITY() AS RetValue";
            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@ManagerId", SqlDbType.NText);
            commandParameters[0].Value = data.ManagerId;
            commandParameters[1] = new SqlParameter("@ManageDeptId", SqlDbType.NText);
            commandParameters[1].Value = data.ManageDeptId;
            object obj2 = SqlHelper.ExecuteScalar(conn, CommandType.Text, commandText, commandParameters);
            if (obj2 != DBNull.Value)
            {
                return Convert.ToInt32(obj2);
            }
            return -1;
        }

        public DataTable SelectAll(SqlConnection conn)
        {
            string commandText = "SELECT [AutoId], [ManagerId], dbo.GetUserNames([ManagerId]) AS ManagerName, [ManageDeptId], dbo.GetDeptNames([ManageDeptId]) AS ManageDeptName FROM [dbo].[Book_Manager] ORDER BY [AutoId] ASC";
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, commandText);
            set.Tables[0].TableName = "Book_Manager";
            return set.Tables[0];
        }

        public DataTable SelectByAutoId(SqlConnection conn, int autoId)
        {
            string commandText = "SELECT [AutoId], [ManagerId], dbo.GetUserNames([ManagerId]) AS ManagerName, [ManageDeptId], dbo.GetDeptNames([ManageDeptId]) AS ManageDeptName FROM [dbo].[Book_Manager] WHERE [AutoId] = @AutoId";
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@AutoId", SqlDbType.Int) };
            commandParameters[0].Value = autoId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, commandText, commandParameters);
            set.Tables[0].TableName = "Book_Manager";
            return set.Tables[0];
        }

        public DataTable SelectByUserId(SqlConnection conn, string userId)
        {
            string commandText = "SELECT [ManageDeptId] FROM [dbo].[Book_Manager] WHERE CHARINDEX(',' + @UserId + ',',',' + CAST([ManagerId] AS nvarchar(2000)) + ',') > 0";
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@UserId", SqlDbType.NVarChar, 20) };
            commandParameters[0].Value = userId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, commandText, commandParameters);
            set.Tables[0].TableName = "Book_Manager";
            return set.Tables[0];
        }

        public bool Update(SqlConnection conn, BookManagerData data)
        {
            string commandText = "UPDATE [dbo].[Book_Manager] SET [ManagerId] = @ManagerId, [ManageDeptId] = @ManageDeptId WHERE [AutoId] = @AutoId";
            SqlParameter[] commandParameters = new SqlParameter[3];
            commandParameters[0] = new SqlParameter("@AutoId", SqlDbType.Int);
            commandParameters[0].Value = data.AutoId;
            commandParameters[1] = new SqlParameter("@ManagerId", SqlDbType.NText);
            commandParameters[1].Value = data.ManagerId;
            commandParameters[2] = new SqlParameter("@ManageDeptId", SqlDbType.NText);
            commandParameters[2].Value = data.ManageDeptId;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, commandText, commandParameters);
            return true;
        }
    }
}

